package com.ruoyi.energy.service.impl;

import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.energy.domain.EmsTimeSeriesData;
import com.ruoyi.energy.service.ITDengineTimeSeriesService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;
import java.util.TimeZone;

/**
 * TDengine时序数据服务实现类
 * 
 * @author ruoyi
 * @date 2025-01-28
 */
@Service
public class TDengineTimeSeriesServiceImpl implements ITDengineTimeSeriesService {

    private static final Logger log = LoggerFactory.getLogger(TDengineTimeSeriesServiceImpl.class);

    @Autowired
    @Qualifier("tdengineJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    private static final String DATABASE_NAME = "ems_data";
    private static final String STABLE_NAME = "ems_time_series";
    private static final SimpleDateFormat SDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");

    static {
        // 设置时区为中国标准时间，避免时区问题
        SDF.setTimeZone(TimeZone.getTimeZone("Asia/Shanghai"));
    }

    @Override
    public boolean createDatabase(String database) {
        try {
            String sql = String.format("CREATE DATABASE IF NOT EXISTS %s KEEP 365 DURATION 10 BUFFER 16", database);
            jdbcTemplate.execute(sql);
            log.info("创建数据库成功: {}", database);
            return true;
        } catch (Exception e) {
            log.error("创建数据库失败: {}", database, e);
            return false;
        }
    }

    @Override
    public boolean createStable(String stableName) {
        try {
            String sql = String.format(
                "CREATE STABLE IF NOT EXISTS %s.%s (" +
                "ts TIMESTAMP, " +
                "power FLOAT, " +
                "total_energy FLOAT, " +
                "instant_energy FLOAT, " +
                "voltage FLOAT, " +
                "current FLOAT, " +
                "power_factor FLOAT, " +
                "flow_rate FLOAT, " +
                "total_flow FLOAT, " +
                "pressure FLOAT, " +
                "temperature FLOAT, " +
                "humidity FLOAT, " +
                "status NCHAR(10), " +
                "quality NCHAR(10), " +
                "extend1 NCHAR(200), " +
                "extend2 NCHAR(200), " +
                "extend3 NCHAR(200)" +
                ") TAGS (device_code NCHAR(50), energy_type NCHAR(20))",
                DATABASE_NAME, stableName
            );
            jdbcTemplate.execute(sql);
            log.info("创建超级表成功: {}", stableName);
            return true;
        } catch (Exception e) {
            log.error("创建超级表失败: {}", stableName, e);
            return false;
        }
    }

    @Override
    public boolean createTable(String tableName, String stableName, String deviceCode, String energyType) {
        try {
            String sql = String.format(
                "CREATE TABLE IF NOT EXISTS %s.%s USING %s.%s TAGS ('%s', '%s')",
                DATABASE_NAME, tableName, DATABASE_NAME, stableName, 
                escapeSql(toString(deviceCode)), escapeSql(toString(energyType))
            );
            jdbcTemplate.execute(sql);
            log.info("创建子表成功: {} for device: {}", tableName, toString(deviceCode));
            return true;
        } catch (Exception e) {
            log.error("创建子表失败: {} for device: {}", tableName, toString(deviceCode), e);
            return false;
        }
    }

    @Override
    public boolean insertTimeSeriesData(EmsTimeSeriesData data) {
        int maxRetries = 3;
        int retryCount = 0;

        while (retryCount < maxRetries) {
            try {
                // 检查连接状态
                if (!checkConnection()) {
                    log.warn("TDengine连接不可用，尝试重新连接... (重试次数: {}/{})", retryCount + 1, maxRetries);
                    Thread.sleep(1000); // 等待1秒后重试
                    retryCount++;
                    continue;
                }

                String tableName = generateTableName(data.getDeviceCode(), data.getEnergyType());

                // 自动创建子表
                createTable(tableName, STABLE_NAME, data.getDeviceCode(), data.getEnergyType());

                // 使用SQL拼接方式避免REST驱动的类型转换问题
                String sql = String.format(
                    "INSERT INTO %s.%s VALUES ('%s', %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, '%s', '%s', '%s', '%s', '%s')",
                    DATABASE_NAME, tableName,
                    SDF.format(data.getTs()),
                    nvlDouble(data.getPower()),
                    nvlDouble(data.getTotalEnergy()),
                    nvlDouble(data.getInstantEnergy()),
                    nvlDouble(data.getVoltage()),
                    nvlDouble(data.getCurrent()),
                    nvlDouble(data.getPowerFactor()),
                    nvlDouble(data.getFlowRate()),
                    nvlDouble(data.getTotalFlow()),
                    nvlDouble(data.getPressure()),
                    nvlDouble(data.getTemperature()),
                    nvlDouble(data.getHumidity()),
                    escapeSql(toString(data.getStatus())),
                    escapeSql(toString(data.getQuality())),
                    escapeSql(toString(data.getExtend1())),
                    escapeSql(toString(data.getExtend2())),
                    escapeSql(toString(data.getExtend3()))
                );

                jdbcTemplate.execute(sql);

                log.debug("插入时序数据成功: {}", data.getDeviceCode());
                return true;

            } catch (Exception e) {
                retryCount++;
                log.error("插入时序数据失败: {} (重试次数: {}/{})", data.getDeviceCode(), retryCount, maxRetries, e);

                if (retryCount >= maxRetries) {
                    log.error("插入时序数据最终失败，已达到最大重试次数: {}", data.getDeviceCode());
                    return false;
                }

                try {
                    Thread.sleep(1000 * retryCount); // 递增等待时间
                } catch (InterruptedException ie) {
                    Thread.currentThread().interrupt();
                    return false;
                }
            }
        }

        return false;
    }

    @Override
    public boolean batchInsertTimeSeriesData(List<EmsTimeSeriesData> dataList) {
        if (dataList == null || dataList.isEmpty()) {
            return true;
        }
        
        try {
            StringBuilder sqlBuilder = new StringBuilder();
            
            // 按设备分组批量插入
            Map<String, List<EmsTimeSeriesData>> deviceDataMap = new HashMap<>();
            for (EmsTimeSeriesData data : dataList) {
                String key = data.getDeviceCode() + "_" + data.getEnergyType();
                deviceDataMap.computeIfAbsent(key, k -> new ArrayList<>()).add(data);
            }
            
            for (Map.Entry<String, List<EmsTimeSeriesData>> entry : deviceDataMap.entrySet()) {
                List<EmsTimeSeriesData> deviceDataList = entry.getValue();
                if (!deviceDataList.isEmpty()) {
                    EmsTimeSeriesData firstData = deviceDataList.get(0);
                    String tableName = generateTableName(firstData.getDeviceCode(), firstData.getEnergyType());
                    
                    // 自动创建子表
                    createTable(tableName, STABLE_NAME, firstData.getDeviceCode(), firstData.getEnergyType());
                    
                    sqlBuilder.append(String.format("INSERT INTO %s.%s VALUES ", DATABASE_NAME, tableName));
                    
                    for (int i = 0; i < deviceDataList.size(); i++) {
                        EmsTimeSeriesData data = deviceDataList.get(i);
                        if (i > 0) {
                            sqlBuilder.append(", ");
                        }
                        sqlBuilder.append(String.format(
                            "('%s', %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, '%s', '%s', '%s', '%s', '%s')",
                            SDF.format(data.getTs()),
                            nvlDouble(data.getPower()),
                            nvlDouble(data.getTotalEnergy()),
                            nvlDouble(data.getInstantEnergy()),
                            nvlDouble(data.getVoltage()),
                            nvlDouble(data.getCurrent()),
                            nvlDouble(data.getPowerFactor()),
                            nvlDouble(data.getFlowRate()),
                            nvlDouble(data.getTotalFlow()),
                            nvlDouble(data.getPressure()),
                            nvlDouble(data.getTemperature()),
                            nvlDouble(data.getHumidity()),
                            escapeSql(toString(data.getStatus())),
                            escapeSql(toString(data.getQuality())),
                            escapeSql(toString(data.getExtend1())),
                            escapeSql(toString(data.getExtend2())),
                            escapeSql(toString(data.getExtend3()))
                        ));
                    }
                    
                    jdbcTemplate.execute(sqlBuilder.toString());
                    sqlBuilder.setLength(0);
                }
            }
            
            log.info("批量插入时序数据成功，共{}条", dataList.size());
            return true;
        } catch (Exception e) {
            log.error("批量插入时序数据失败", e);
            return false;
        }
    }

    @Override
    public EmsTimeSeriesData selectLatestDataByDevice(String deviceCode) {
        try {
            // 使用ORDER BY ts DESC LIMIT 1的方式获取最新数据
            String sql = String.format(
                "SELECT ts, device_code, energy_type, power, total_energy, instant_energy, voltage, current, " +
                "power_factor, flow_rate, total_flow, pressure, temperature, humidity, status, quality, " +
                "extend1, extend2, extend3 FROM %s.%s WHERE device_code = ? ORDER BY ts DESC LIMIT 1",
                DATABASE_NAME, STABLE_NAME
            );

            List<EmsTimeSeriesData> result = jdbcTemplate.query(sql, new Object[]{toString(deviceCode)}, new TimeSeriesDataRowMapper());
            return result.isEmpty() ? null : result.get(0);
        } catch (Exception e) {
            log.error("查询设备最新数据失败: {}", toString(deviceCode), e);
            return null;
        }
    }

    @Override
    public List<EmsTimeSeriesData> selectHistoryDataByDevice(String deviceCode, Date startTime, Date endTime) {
        try {
            String sql = String.format(
                "SELECT * FROM %s.%s WHERE device_code = ? AND ts >= ? AND ts <= ? ORDER BY ts DESC",
                DATABASE_NAME, STABLE_NAME
            );
            
            return jdbcTemplate.query(sql, 
                new Object[]{toString(deviceCode), SDF.format(startTime), SDF.format(endTime)}, 
                new TimeSeriesDataRowMapper());
        } catch (Exception e) {
            log.error("查询设备历史数据失败: {}", toString(deviceCode), e);
            return new ArrayList<>();
        }
    }

    @Override
    public List<EmsTimeSeriesData> selectHistoryDataByDeviceWithPage(String deviceCode, Date startTime, Date endTime, 
                                                                     int limit, int offset) {
        try {
            String sql = String.format(
                "SELECT * FROM %s.%s WHERE device_code = ? AND ts >= ? AND ts <= ? ORDER BY ts DESC LIMIT ? OFFSET ?",
                DATABASE_NAME, STABLE_NAME
            );
            
            return jdbcTemplate.query(sql, 
                new Object[]{toString(deviceCode), SDF.format(startTime), SDF.format(endTime), limit, offset}, 
                new TimeSeriesDataRowMapper());
        } catch (Exception e) {
            log.error("查询设备历史数据（分页）失败: {}", toString(deviceCode), e);
            return new ArrayList<>();
        }
    }

    @Override
    public List<Map<String, Object>> selectAggregatedDataByDevice(String deviceCode, Date startTime, Date endTime, String interval) {
        try {
            String sql = String.format(
                "SELECT _wstart, AVG(power) as avg_power, MAX(power) as max_power, MIN(power) as min_power, " +
                "SUM(instant_energy) as total_energy FROM %s.%s " +
                "WHERE device_code = ? AND ts >= ? AND ts <= ? " +
                "INTERVAL(%s) ORDER BY _wstart",
                DATABASE_NAME, STABLE_NAME, interval
            );

            log.info("执行聚合查询SQL: {}, 参数: {}, {}, {}", sql, deviceCode, SDF.format(startTime), SDF.format(endTime));
            List<Map<String, Object>> result = jdbcTemplate.queryForList(sql, toString(deviceCode), SDF.format(startTime), SDF.format(endTime));
            log.info("聚合查询结果数量: {}", result.size());

            // 如果没有数据，尝试查询该设备是否有任何数据
            if (result.isEmpty()) {
                String checkSql = String.format("SELECT COUNT(*) as count FROM %s.%s WHERE device_code = ?", DATABASE_NAME, STABLE_NAME);
                List<Map<String, Object>> countResult = jdbcTemplate.queryForList(checkSql, toString(deviceCode));
                log.info("设备 {} 总数据量: {}", deviceCode, countResult.isEmpty() ? 0 : countResult.get(0).get("count"));

                // 如果设备有数据，返回最新的一条作为聚合结果
                if (!countResult.isEmpty() && ((Number) countResult.get(0).get("count")).longValue() > 0) {
                    EmsTimeSeriesData latestData = selectLatestDataByDevice(deviceCode);
                    if (latestData != null) {
                        Map<String, Object> mockResult = new HashMap<>();
                        mockResult.put("_wstart", latestData.getTs());
                        mockResult.put("avg_power", latestData.getPower());
                        mockResult.put("max_power", latestData.getPower());
                        mockResult.put("min_power", latestData.getPower());
                        mockResult.put("total_energy", latestData.getTotalEnergy());
                        result.add(mockResult);
                    }
                }
            }

            return result;
        } catch (Exception e) {
            log.error("查询设备聚合数据失败: {}", toString(deviceCode), e);
            return new ArrayList<>();
        }
    }

    @Override
    public Map<String, EmsTimeSeriesData> selectLatestDataByDevices(List<String> deviceCodes) {
        Map<String, EmsTimeSeriesData> result = new HashMap<>();
        for (String deviceCode : deviceCodes) {
            EmsTimeSeriesData data = selectLatestDataByDevice(deviceCode);
            if (data != null) {
                result.put(deviceCode, data);
            }
        }
        return result;
    }

    @Override
    public Map<String, Object> selectEnergyStatistics(List<String> deviceCodes, String energyType, 
                                                       Date startTime, Date endTime) {
        try {
            String deviceCodesStr = String.join("','", deviceCodes);
            String sql = String.format(
                "SELECT device_code, SUM(instant_energy) as total_energy, AVG(power) as avg_power, " +
                "MAX(power) as max_power, MIN(power) as min_power FROM %s.%s " +
                "WHERE device_code IN ('%s') AND energy_type = ? AND ts >= ? AND ts <= ? " +
                "GROUP BY device_code",
                DATABASE_NAME, STABLE_NAME, deviceCodesStr
            );
            
            List<Map<String, Object>> resultList = jdbcTemplate.queryForList(sql, 
                energyType, SDF.format(startTime), SDF.format(endTime));
            
            Map<String, Object> statistics = new HashMap<>();
            statistics.put("devices", resultList);
            return statistics;
        } catch (Exception e) {
            log.error("查询能耗统计数据失败", e);
            return new HashMap<>();
        }
    }

    @Override
    public Map<String, Integer> selectDeviceStatusStatistics(List<String> deviceCodes) {
        Map<String, Integer> result = new HashMap<>();
        try {
            for (String deviceCode : deviceCodes) {
                EmsTimeSeriesData latestData = selectLatestDataByDevice(deviceCode);
                if (latestData != null && latestData.getStatus() != null) {
                    String status = latestData.getStatus();
                    result.put(status, result.getOrDefault(status, 0) + 1);
                }
            }
        } catch (Exception e) {
            log.error("查询设备状态统计失败", e);
        }
        return result;
    }

    @Override
    public boolean deleteDataByTimeRange(String deviceCode, Date startTime, Date endTime) {
        try {
            String sql = String.format(
                "DELETE FROM %s.%s WHERE device_code = ? AND ts >= ? AND ts <= ?",
                DATABASE_NAME, STABLE_NAME
            );
            
            jdbcTemplate.update(sql, toString(deviceCode), SDF.format(startTime), SDF.format(endTime));
            log.info("删除设备时序数据成功: {}", toString(deviceCode));
            return true;
        } catch (Exception e) {
            log.error("删除设备时序数据失败: {}", toString(deviceCode), e);
            return false;
        }
    }

    @Override
    public boolean checkConnection() {
        try {
            jdbcTemplate.queryForObject("SELECT SERVER_VERSION()", String.class);
            return true;
        } catch (Exception e) {
            log.error("TDengine连接检查失败", e);
            return false;
        }
    }

    /**
     * 生成子表名称
     */
    private String generateTableName(String deviceCode, String energyType) {
        // 安全转换字符串，避免字节数组问题
        String safeDeviceCode = toString(deviceCode).replaceAll("[^a-zA-Z0-9]", "_");
        String safeEnergyType = toString(energyType).replaceAll("[^a-zA-Z0-9]", "_");
        return String.format("t_%s_%s", safeDeviceCode, safeEnergyType);
    }

    /**
     * 空值处理
     */
    private String nvl(Object value) {
        return value == null ? "NULL" : value.toString();
    }

    /**
     * 字符串空值处理
     */
    private String nvl(String value, String defaultValue) {
        return value == null ? defaultValue : value;
    }

    /**
     * BigDecimal转Double - TDengine不支持BigDecimal类型
     */
    private Double toDouble(BigDecimal value) {
        return value == null ? null : value.doubleValue();
    }

    /**
     * 安全字符串转换 - 处理字节数组和null值
     */
    private String toString(Object value) {
        if (value == null) {
            return "";
        }
        if (value instanceof byte[]) {
            try {
                return new String((byte[]) value, "UTF-8");
            } catch (Exception e) {
                log.warn("字节数组转字符串失败", e);
                return "";
            }
        }
        return value.toString();
    }

    /**
     * BigDecimal转Double的空值处理 - 用于SQL拼接
     */
    private String nvlDouble(BigDecimal value) {
        return value == null ? "NULL" : String.valueOf(value.doubleValue());
    }

    // ==================== 新增业务查询方法实现 ====================

    @Override
    public List<EmsTimeSeriesData> selectAllDevicesLatestData() {
        try {
            // 使用LAST函数获取每个设备的最新数据，GROUP BY需要包含所有TAG列
            String sql = String.format(
                "SELECT LAST(ts) as ts, device_code, energy_type, " +
                "LAST(power) as power, LAST(total_energy) as total_energy, LAST(instant_energy) as instant_energy, " +
                "LAST(voltage) as voltage, LAST(current) as current, LAST(power_factor) as power_factor, " +
                "LAST(flow_rate) as flow_rate, LAST(total_flow) as total_flow, LAST(pressure) as pressure, " +
                "LAST(temperature) as temperature, LAST(humidity) as humidity, " +
                "LAST(status) as status, LAST(quality) as quality, " +
                "LAST(extend1) as extend1, LAST(extend2) as extend2, LAST(extend3) as extend3 " +
                "FROM %s.%s GROUP BY device_code, energy_type ORDER BY device_code",
                DATABASE_NAME, STABLE_NAME
            );

            return jdbcTemplate.query(sql, new TimeSeriesDataRowMapper());
        } catch (Exception e) {
            log.error("查询所有设备最新数据失败", e);
            return new ArrayList<>();
        }
    }

    @Override
    public long countDeviceData(String deviceCode, Date startTime, Date endTime) {
        try {
            StringBuilder sql = new StringBuilder();
            sql.append(String.format("SELECT COUNT(*) FROM %s.%s WHERE 1=1", DATABASE_NAME, STABLE_NAME));

            List<Object> params = new ArrayList<>();

            if (StringUtils.isNotEmpty(deviceCode)) {
                sql.append(" AND device_code = ?");
                params.add(deviceCode);
            }

            if (startTime != null) {
                sql.append(" AND ts >= ?");
                params.add(SDF.format(startTime));
            }

            if (endTime != null) {
                sql.append(" AND ts <= ?");
                params.add(SDF.format(endTime));
            }

            Long count = jdbcTemplate.queryForObject(sql.toString(), params.toArray(), Long.class);
            return count != null ? count : 0L;
        } catch (Exception e) {
            log.error("查询设备数据总数失败", e);
            return 0L;
        }
    }

    @Override
    public Map<String, Long> selectDeviceOnlineStatistics() {
        try {
            // 使用TDengine兼容的语法：先查询每个设备的最新状态，然后在Java中统计
            String sql = String.format(
                "SELECT device_code, LAST(status) as status FROM %s.%s GROUP BY device_code",
                DATABASE_NAME, STABLE_NAME
            );

            List<Map<String, Object>> result = jdbcTemplate.queryForList(sql);
            Map<String, Long> statistics = new HashMap<>();
            statistics.put("online", 0L);
            statistics.put("offline", 0L);

            for (Map<String, Object> row : result) {
                String status = (String) row.get("status");

                // 根据状态值判断在线/离线状态
                if (status != null) {
                    // 状态映射：0-正常(在线), 1-告警(在线), 2-故障(离线), 3-离线
                    if ("0".equals(status) || "1".equals(status) || "ONLINE".equals(status) || "ALARM".equals(status)) {
                        statistics.put("online", statistics.get("online") + 1);
                    } else {
                        statistics.put("offline", statistics.get("offline") + 1);
                    }
                } else {
                    // 状态为空视为离线
                    statistics.put("offline", statistics.get("offline") + 1);
                }
            }

            return statistics;
        } catch (Exception e) {
            log.error("查询设备在线状态统计失败", e);
            // 返回默认值
            Map<String, Long> defaultStats = new HashMap<>();
            defaultStats.put("online", 0L);
            defaultStats.put("offline", 0L);
            return defaultStats;
        }
    }

    @Override
    public List<Map<String, Object>> selectDeviceEnergyRanking(String energyType, Date startTime, Date endTime, int limit) {
        try {
            // 参数验证
            if (startTime == null || endTime == null) {
                log.warn("查询设备能耗排行榜时，开始时间或结束时间为空");
                return new ArrayList<>();
            }

            // 首先尝试按能源类型查询
            String sql = String.format(
                "SELECT device_code, SUM(total_energy) as total_consumption " +
                "FROM %s.%s WHERE energy_type = ? AND ts >= ? AND ts <= ? " +
                "GROUP BY device_code ORDER BY total_consumption DESC LIMIT ?",
                DATABASE_NAME, STABLE_NAME
            );

            List<Map<String, Object>> result = jdbcTemplate.queryForList(sql, energyType, formatDate(startTime), formatDate(endTime), limit);

            // 如果没有数据，尝试不按能源类型过滤，直接按设备分组
            if (result.isEmpty()) {
                log.info("按能源类型 {} 查询无数据，尝试查询所有设备数据", energyType);
                String sqlAll = String.format(
                    "SELECT device_code, SUM(total_energy) as total_consumption " +
                    "FROM %s.%s WHERE ts >= ? AND ts <= ? " +
                    "GROUP BY device_code ORDER BY total_consumption DESC LIMIT ?",
                    DATABASE_NAME, STABLE_NAME
                );
                result = jdbcTemplate.queryForList(sqlAll, formatDate(startTime), formatDate(endTime), limit);
            }

            log.info("查询设备能耗排行榜，能源类型: {}, 时间范围: {} - {}, 结果数量: {}",
                energyType, formatDate(startTime), formatDate(endTime), result.size());

            return result;
        } catch (Exception e) {
            log.error("查询设备能耗排行榜失败，能源类型: {}", energyType, e);
            return new ArrayList<>();
        }
    }

    @Override
    public List<Map<String, Object>> selectDeviceAlarmData(String deviceCode, Date startTime, Date endTime) {
        try {
            String sql = String.format(
                "SELECT ts, device_code, power, voltage, current, temperature, pressure, status, quality " +
                "FROM %s.%s WHERE device_code = ? AND ts >= ? AND ts <= ? " +
                "AND (quality != 'GOOD' OR status != 'ONLINE' OR power > 1000 OR temperature > 80) " +
                "ORDER BY ts DESC",
                DATABASE_NAME, STABLE_NAME
            );

            return jdbcTemplate.queryForList(sql, deviceCode, SDF.format(startTime), SDF.format(endTime));
        } catch (Exception e) {
            log.error("查询设备告警数据失败: {}", deviceCode, e);
            return new ArrayList<>();
        }
    }

    @Override
    public Map<String, Object> selectDeviceEfficiencyAnalysis(String deviceCode, Date startTime, Date endTime) {
        try {
            String sql = String.format(
                "SELECT " +
                "AVG(power_factor) as avg_power_factor, " +
                "AVG(power) as avg_power, " +
                "MAX(power) as max_power, " +
                "MIN(power) as min_power, " +
                "SUM(total_energy) as total_energy, " +
                "COUNT(*) as data_points " +
                "FROM %s.%s WHERE device_code = ? AND ts >= ? AND ts <= ?",
                DATABASE_NAME, STABLE_NAME
            );

            List<Map<String, Object>> result = jdbcTemplate.queryForList(sql, deviceCode, SDF.format(startTime), SDF.format(endTime));
            return result.isEmpty() ? new HashMap<>() : result.get(0);
        } catch (Exception e) {
            log.error("查询设备效率分析数据失败: {}", deviceCode, e);
            return new HashMap<>();
        }
    }

    @Override
    public List<Map<String, Object>> selectDeviceLoadAnalysis(String deviceCode, Date startTime, Date endTime, String interval) {
        try {
            String sql = String.format(
                "SELECT _wstart as time_window, " +
                "AVG(power) as avg_load, " +
                "MAX(power) as peak_load, " +
                "MIN(power) as min_load, " +
                "AVG(power_factor) as avg_power_factor " +
                "FROM %s.%s WHERE device_code = ? AND ts >= ? AND ts <= ? " +
                "INTERVAL(%s) ORDER BY _wstart",
                DATABASE_NAME, STABLE_NAME, interval
            );

            return jdbcTemplate.queryForList(sql, deviceCode, SDF.format(startTime), SDF.format(endTime));
        } catch (Exception e) {
            log.error("查询设备负载分析数据失败: {}", deviceCode, e);
            return new ArrayList<>();
        }
    }

    @Override
    public List<Map<String, Object>> selectMultiDeviceComparisonData(List<String> deviceCodes, Date startTime, Date endTime, String interval) {
        try {
            if (deviceCodes == null || deviceCodes.isEmpty()) {
                return new ArrayList<>();
            }

            String deviceCodesStr = deviceCodes.stream()
                .map(code -> "'" + code + "'")
                .collect(Collectors.joining(","));

            String sql = String.format(
                "SELECT _wstart as time_window, device_code, " +
                "AVG(power) as avg_power, " +
                "SUM(total_energy) as total_energy " +
                "FROM %s.%s WHERE device_code IN (%s) AND ts >= ? AND ts <= ? " +
                "INTERVAL(%s) GROUP BY device_code ORDER BY _wstart, device_code",
                DATABASE_NAME, STABLE_NAME, deviceCodesStr, interval
            );

            return jdbcTemplate.queryForList(sql, SDF.format(startTime), SDF.format(endTime));
        } catch (Exception e) {
            log.error("查询多设备对比数据失败", e);
            return new ArrayList<>();
        }
    }

    @Override
    public List<Map<String, Object>> selectDeviceDataForExport(String deviceCode, Date startTime, Date endTime, List<String> fields) {
        try {
            String fieldStr = fields != null && !fields.isEmpty() ?
                String.join(",", fields) : "*";

            String sql = String.format(
                "SELECT %s FROM %s.%s WHERE device_code = ? AND ts >= ? AND ts <= ? ORDER BY ts DESC",
                fieldStr, DATABASE_NAME, STABLE_NAME
            );

            return jdbcTemplate.queryForList(sql, deviceCode, SDF.format(startTime), SDF.format(endTime));
        } catch (Exception e) {
            log.error("查询设备数据导出失败: {}", deviceCode, e);
            return new ArrayList<>();
        }
    }

    @Override
    public Map<String, Object> selectDeviceDataQualityStatistics(String deviceCode, Date startTime, Date endTime) {
        try {
            String sql = String.format(
                "SELECT " +
                "COUNT(*) as total_records, " +
                "SUM(CASE WHEN quality = 'GOOD' THEN 1 ELSE 0 END) as good_quality_records, " +
                "SUM(CASE WHEN status = 'ONLINE' THEN 1 ELSE 0 END) as online_records, " +
                "SUM(CASE WHEN power IS NOT NULL THEN 1 ELSE 0 END) as valid_power_records " +
                "FROM %s.%s WHERE device_code = ? AND ts >= ? AND ts <= ?",
                DATABASE_NAME, STABLE_NAME
            );

            List<Map<String, Object>> result = jdbcTemplate.queryForList(sql, deviceCode, SDF.format(startTime), SDF.format(endTime));
            return result.isEmpty() ? new HashMap<>() : result.get(0);
        } catch (Exception e) {
            log.error("查询设备数据质量统计失败: {}", deviceCode, e);
            return new HashMap<>();
        }
    }

    @Override
    public Map<String, Object> selectDevicePeakValleyAnalysis(String deviceCode, Date startTime, Date endTime) {
        try {
            String sql = String.format(
                "SELECT " +
                "AVG(CASE WHEN HOUR(ts) BETWEEN 8 AND 11 OR HOUR(ts) BETWEEN 18 AND 22 THEN power END) as peak_avg_power, " +
                "AVG(CASE WHEN HOUR(ts) BETWEEN 23 AND 7 THEN power END) as valley_avg_power, " +
                "AVG(CASE WHEN HOUR(ts) BETWEEN 12 AND 17 THEN power END) as normal_avg_power, " +
                "SUM(CASE WHEN HOUR(ts) BETWEEN 8 AND 11 OR HOUR(ts) BETWEEN 18 AND 22 THEN total_energy END) as peak_energy, " +
                "SUM(CASE WHEN HOUR(ts) BETWEEN 23 AND 7 THEN total_energy END) as valley_energy, " +
                "SUM(CASE WHEN HOUR(ts) BETWEEN 12 AND 17 THEN total_energy END) as normal_energy " +
                "FROM %s.%s WHERE device_code = ? AND ts >= ? AND ts <= ?",
                DATABASE_NAME, STABLE_NAME
            );

            List<Map<String, Object>> result = jdbcTemplate.queryForList(sql, deviceCode, SDF.format(startTime), SDF.format(endTime));
            return result.isEmpty() ? new HashMap<>() : result.get(0);
        } catch (Exception e) {
            log.error("查询设备峰谷电分析失败: {}", deviceCode, e);
            return new HashMap<>();
        }
    }

    /**
     * 安全的日期格式化方法 - 防止空指针异常
     */
    private String formatDate(Date date) {
        if (date == null) {
            throw new IllegalArgumentException("日期参数不能为空");
        }
        return SDF.format(date);
    }

    /**
     * SQL字符串转义 - 防止SQL注入
     */
    private String escapeSql(String value) {
        if (value == null) {
            return "";
        }
        // 转义单引号和其他特殊字符
        return value.replace("'", "''")
                   .replace("\\", "\\\\")
                   .replace("\n", "\\n")
                   .replace("\r", "\\r")
                   .replace("\t", "\\t");
    }

    /**
     * 时序数据行映射器
     */
    private static class TimeSeriesDataRowMapper implements RowMapper<EmsTimeSeriesData> {
        @Override
        public EmsTimeSeriesData mapRow(ResultSet rs, int rowNum) throws SQLException {
            EmsTimeSeriesData data = new EmsTimeSeriesData();
            data.setTs(rs.getTimestamp("ts"));
            data.setDeviceCode(rs.getString("device_code"));
            data.setEnergyType(rs.getString("energy_type"));
            data.setPower(getBigDecimal(rs, "power"));
            data.setTotalEnergy(getBigDecimal(rs, "total_energy"));
            data.setInstantEnergy(getBigDecimal(rs, "instant_energy"));
            data.setVoltage(getBigDecimal(rs, "voltage"));
            data.setCurrent(getBigDecimal(rs, "current"));
            data.setPowerFactor(getBigDecimal(rs, "power_factor"));
            data.setFlowRate(getBigDecimal(rs, "flow_rate"));
            data.setTotalFlow(getBigDecimal(rs, "total_flow"));
            data.setPressure(getBigDecimal(rs, "pressure"));
            data.setTemperature(getBigDecimal(rs, "temperature"));
            data.setHumidity(getBigDecimal(rs, "humidity"));
            data.setStatus(rs.getString("status"));
            data.setQuality(rs.getString("quality"));
            data.setExtend1(rs.getString("extend1"));
            data.setExtend2(rs.getString("extend2"));
            data.setExtend3(rs.getString("extend3"));
            return data;
        }

        private BigDecimal getBigDecimal(ResultSet rs, String columnName) throws SQLException {
            Object value = rs.getObject(columnName);
            return value == null ? null : new BigDecimal(value.toString());
        }
    }
} 